package com.example.ccsystem.service;

import com.example.ccsystem.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.example.ccsystem.entity.Teacher;

@Service
public class TeacherServiceImpt implements TeacherService{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public String getAllTeachers(){
        return jdbcTemplate.queryForObject("select count(1) from Teacher",String.class);
    }

    @Override
    public List<Map<String,Object>> findAllTeacher(){
        String sql="select * from Teacher";
        List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
        return list;
    }

    @Override
    public Teacher getByTId(int tid){
        String sql="select * from Teacher where tid=?";
        List<Teacher> tea=jdbcTemplate.query(sql,new Object[]{tid},new TeacherRowMapper());

        Teacher teacher=null;
        if(!tea.isEmpty()){
            teacher=tea.get(0);
        }

        return teacher;
    }

    /**
     * add teacher
     * @param teacher
     * @return
     */
    @Override
    public int addTea(Teacher teacher){
        String sql="insert into Teacher(tid,tname,work,college,isadmin) values(null,?,?,?,?)";
        KeyHolder keyHolder=new GeneratedKeyHolder();
        int resRow=jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

                PreparedStatement ps=con.prepareStatement(sql,new String[]{"tid"});
                ps.setString(1,teacher.getTname());
                ps.setString(2,teacher.getWork());
                ps.setString(3,teacher.getCollege());
                ps.setInt(4,teacher.getIsadmin());
                return ps;

            }
        },keyHolder);
        System.out.println("操作记录数："+resRow+"主键："+keyHolder.getKey());
        return Integer.parseInt(keyHolder.getKey().toString());
    }

    @Override
    public int deleteTea(int tid){
        String sql="delete from Teacher where tid=?";
        return jdbcTemplate.update(sql,tid);
    }

    @Override
    public int updateTea(Teacher teacher){
        String sql="update Teacher set tname=?,work=?,college=?,isadmin=? where tid=?";
        int res=jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {

                ps.setString(1,teacher.getTname());
                ps.setString(2,teacher.getWork());
                ps.setString(3,teacher.getCollege());
                ps.setInt(4,teacher.getIsadmin());
            }
        });
        return res;
    }

    @Override
    public int isHasTea(int tid){
        String sql="select * from Teacher where tid=?";
        List<Teacher> teacher=jdbcTemplate.query(sql,new Object[]{tid},
                new TeacherRowMapper());

        if(teacher!=null&&teacher.size()>0){
            return 1;
        }else {
            return 0;
        }
    }
}
class TeacherRowMapper implements RowMapper<Teacher>{

    @Override
    public Teacher mapRow(ResultSet resultSet,int i) throws SQLException{

        Teacher tea=new Teacher();
        tea.setTid(resultSet.getInt("tid"));
        tea.setTname(resultSet.getString("tname"));
        tea.setWork(resultSet.getString("work"));
        tea.setCollege(resultSet.getString("college"));
        tea.setIsadmin(resultSet.getInt("isadmin"));

        return tea;
    }
}
